---
layout: default
path: google-apps
title: Google Apps Scripting
---
# Google Apps Scripting
---
It all started with a problem...
--
--
--
I literally couldn't publish a CSV to the open web.
--
This was dumb.
--
There had to be a solution.
---
After much google searching, I happened upon [Google Apps Script](https://developers.google.com/apps-script/)
--
--
Sure, I had heard of it.
--
Even tried it.
--
Never really _thought_ about it.
--
(Disclosure: The UI reminded me of Visual Basic).
--
--
But, it had a REST API under the hood, so...
---
# First Pass
--
https://github.com/18F/sheet-to-csv
--
`node index SHEET_ID > out.csv`
--
Uses a "Target" (i.e., an event listener)
--
--
``` js
/**
* This function returns a CSV from Google Drive using the file's id
* @param {string} fileId the Google Sheet's fileId
* @return {string} a csv string
**/
function getCSVbyId(fileId) {
var f = SpreadsheetApp.openById(fileId).getActiveSheet().getDataRange().getValues();
var csv = "";
for (i in f) {
csv += f[i].join(',') + "\n";
}
return csv;
}
```
--
`var f = SpreadsheetApp.openById(fileId).getActiveSheet().getDataRange().getValues();`
--
But still need something to *call* the Target.
--
--
Uses OAuth 2.0 for authentication.
--
[Warning: When OAuth is involved, pay attention to scopes!]
--
Anyway, a few tweaks later...
--
`node index SHEET_ID > out.csv`
--
---
# The Second Pass
--
We can do *better*
--
--
There's also that REST API...
--
--
--
--
Now we can make https://github.com/18F/gapps-download
--
``` bash
node index 12345 text/html > out.html # Download a google doc to `out.html`
node index 12345 text/csv > out.csv # Download a google sheet to `out.csv`
node index 12345 > out.pdf # Download a google doc as a pdf
node index 12345 application/vnd.openxmlformats-officedocument.wordprocessingml.document > out.docx # Download google doc as a MS Word Document
```
--
And, because we're using the REST API, no Target is needed.
--
[Warning: When OAuth is involved, pay attention to scopes!]
--
Just a single node script, and now I can download my CSV natively!
--
And html, docx, etc.
--
---
# Moar Google Scripting!
--
Micro-purchase experiment involves a Google Form
--
That means **scripting**!
--
On formSubmit:
1. Update github
2. Update Form
--
--
Remember Triggers? (the Event Listener?)
--
--
``` js
function githubPost() {
var ss = SpreadsheetApp.openById(ssID) // replace the ssID with the Spreadsheet ID
var sheet = ss.getSheetByName("Form Responses 1");
var formResponses = FormApp.getActiveForm().getResponses();
var res = formResponses[formResponses.length-1].getItemResponses();
var amt = res[0].getResponse();
var title = "Load Schedule 70 data into CALC. >>> Current bid: " + amt + " <<<"
var payload = {
"title": title
}
var url = "https://api.github.com/repos/18f/calc/issues/255?access_token=" + ACCESS_TOKEN // Add the Github Access Token
var resp = UrlFetchApp.fetch(url, {method: "patch", payload: JSON.stringify(payload)});
var bidItem = FormApp.getActiveForm().getItems()[0].setHelpText("Your bid may not exceed the lowest bid amount. Currently, the >>> lowest bid amount is $" + amt + "<<<.")
}
```
--
What's going on here?
--
Get the Form Responses
``` js
var formResponses = FormApp.getActiveForm().getResponses();
var res = formResponses[formResponses.length-1].getItemResponses();
var amt = res[0].getResponse();
```
--
Change title in GitHub
``` js
var title = "Load Schedule 70 data into CALC. >>> Current bid: " + amt + " <<<"
var payload = {
"title": title
}
var url = "https://api.github.com/repos/18f/calc/issues/255?access_token=" + ACCESS_TOKEN // Add the Github Access Token
var resp = UrlFetchApp.fetch(url, {method: "patch", payload: JSON.stringify(payload)});
```
--
That's right, you can PATCH to GitHub from Google Apps
--
--
And I can even change the current Form, because Google Apps!
``` js
var bidItem =
FormApp.getActiveForm().getItems()[0]
.setHelpText("Your bid may not exceed the lowest bid amount. Currently, the >>> lowest bid amount is $" + amt + "<<<.")
```
--
But, the google script was a little buggy...
--
So, I had to open the response sheet first. ¯\\\_(ツ)\_/¯
``` js
var ss = SpreadsheetApp.openById(ssID) // replace the ssID with the Spreadsheet ID
var sheet = ss.getSheetByName("Form Responses 1");
```
--
---
# What's next?
--
So, here's the thing.
--
Google Apps Script supports all of the Google Apps
--
--
That means we could:
* schedule meetings from slack!
* auto-publish from docs to github!
* automate onboarding!
* automate mandatory training compliance!
* and more!
--
---
Anything that involves a REST API suddenly becomes interoperable with:
* your word processor
* your spreadsheet
* your email
* you calendar
--
The future of business automation?
Probably not.
--
But I sure am glad that the public sharing was set to off.
--
--
Thank you!